<?php
require_once('../application/models/Product.php');
require_once('../application/models/Category.php');

class AdminBusiness
{
	//Métodos cadastro/update
	public function insertProducts ($array)
	{
		try {
			$db = Zend_Db_Table::getDefaultAdapter();
			$lastId = $db->lastInsertId('pr_product');
			$array['id_product'] = $lastId;
			$query = $db->insert('pr_product', $array);

		} catch (Exception $e) {
			echo $e;
		}
	}

	public function editProducts($array) {
		try {

			$db = Zend_Db_Table::getDefaultAdapter();
			$query = "UPDATE pr_product SET id_category ='" .$array['id_category'].
			"', name='" .$array['name']. "', title='".$array['title'].
			"', nationality='".$array['nationality']."', year='".$array['year'].
			"',price='".$array['price']."',quantity=".$array['quantity'].",weight=".$array['weight'];

			if($array['image'] != null){
				$query = $query. ", image = '".$array['image']."'";
			}

			$query = $query. " WHERE id_product=".$array['id_product']."";
				
			$db->query($query);
		} catch (Exception $e) {
			echo $e;
		}
	}

	public function insertEnterptise($array){
		try {
			$db = Zend_Db_Table::getDefaultAdapter();
			$lastId = $db->lastInsertId('pr_enterprise');
			$array['enterprise_id'] = $lastId;
			$query = $db->insert('pr_enterprise', $array);

		} catch (Exception $e) {
			echo $e;
		}
	}

	public function deleteEnterptise($id){
		try {
			$db = Zend_Db_Table::getDefaultAdapter();
			$where = $db->quoteInto('enterprise_id = ?', $id);
			$query = $db->delete('pr_enterprise', $where);

		} catch (Exception $e) {
			echo $e;
		}
	}

	public function deleteCategory($id){
		try {
			$db = Zend_Db_Table::getDefaultAdapter();
			$where = $db->quoteInto('id_category = ?', $id);
			$query = $db->delete('pr_category', $where);

		} catch (Exception $e) {
			echo $e;
		}
	}

	public function insertCategory($array){
		try {
			$db = Zend_Db_Table::getDefaultAdapter();
			$lastId = $db->lastInsertId('pr_category');
			$array['id_category'] = $lastId;
			$query = $db->insert('pr_category', $array);

		} catch (Exception $e) {
			echo $e;
		}
	}

	public function changeProductStatus($id, $status)
	{
		try {
			//Adapter do banco
			$db = Zend_Db_Table::getDefaultAdapter();
			//Condicao do select
			$whereSelect = "id_product = '". $id ."'";
			//Busca o produto de acordo com o id passado
			$query = $db->select()
			->from('pr_product')
			->where($whereSelect);
			//Armazena o resultado da busca
			$array = $db->fetchAll($query);
			//Altera o status do produto de acordo com o parametro
			if($array[0]['status'] == "1") {
				$status = '0';
			} else {
				$status = '1';
			}
			echo "UPDATE pr_product SET status ='" .$status. "' WHERE id_product ='" .$id. "'";
			//Executa o update
			$update = "UPDATE pr_product SET status ='" .$status. "' WHERE id_product ='" .$id. "'";
			$db->query($update);

		} catch (Exception $e) {
		}
	}


	//Métodos busca
	public function getProducts($search)
	{
		//Adaptador para o banco (Tabela não existe na nossa Base)
		try {

			$db = Zend_Db_Table::getDefaultAdapter();
			//Query de consulta
			$query = "SELECT * FROM pr_product";

			if($search != null){
				$query = $query . " where title like '%".$search."%' or name like '%".$search."%' or year like '%".$search."%' or price like '%".$search."%' or quantity like '%".$search."%'";
			}

			$select = $db->fetchAll($query);
			return $select;
		} catch (Exception $e) {
			echo $e;
		}
	}

	public function getAllCategories()
	{
		try {
			$db = Zend_Db_Table::getDefaultAdapter();

			$query = $db->select()
			->from('pr_category');
			$select = $db->fetchAll($query);

			return $select;
		} catch (Exception $e) {
		}
	}

	public function getAllEnterprises()
	{
		try {
			$db = Zend_Db_Table::getDefaultAdapter();

			$query = $db->select()
			->from('pr_enterprise')
			->order('enterprise_name ASC');
			$select = $db->fetchAll($query);
			return $select;
		} catch (Exception $e) {
		}
	}

	public function getAllTransactions($city){
		try {
			$db = Zend_Db_Table::getDefaultAdapter();

			$query = "SELECT distinct *
			FROM cl_transaction ct
			INNER JOIN cl_transaction_address ta
			on ta.id_transaction = ct.id_transaction
			INNER JOIN cl_client cl
			on cl.id_client = ct.id_client
			INNER JOIN cl_address ca
			on cl.id_client = ca.id_client
			INNER JOIN tr_status ts
			on ts.id_status = ct.status";
				
			if($city){
				$query = $query . " WHERE lower(ta.city) = lower('".$city."')";
			}else{
				$query = $query . " WHERE lower(ta.city) <> lower('araraquara')";
			}
				
			$select = $db->fetchAll($query);

			return $select;
		} catch (Exception $e) {
		}
	}

	public function getTransactions($search, $status, $initial, $final, $city){

		//Adaptador para o banco (Tabela não existe na nossa Base)
		try {

			$db = Zend_Db_Table::getDefaultAdapter();
			//Query de consulta
			$query = "SELECT distinct *
			FROM cl_transaction ct
			INNER JOIN cl_transaction_address ta
			on ta.id_transaction = ct.id_transaction
			INNER JOIN cl_client cl
			on cl.id_client = ct.id_client
			INNER JOIN cl_address ca
			on cl.id_client = ca.id_client
			INNER JOIN tr_status ts
			on ts.id_status = ct.status";

			if($city != null){
				$query = $query . " WHERE lower(ta.city) = lower('".$city."')";
			}else{
				$query = $query . " WHERE lower(ta.city) <> lower('araraquara')";
			}

			if($search != null){
				$query = $query . " and cl.name = '".$search."' or ct.id_transaction = '".$search."' or cl.email = '".$search."'
				or cl.phone = '".$search."'";
			}

			if($status != null){
				$query = $query . " and ts.id_status = ".$status." ";
			}

			if($initial != null){
				$query = $query . " and ct.date >= '".$initial."' ";
			}

			if($final != null){
				$query = $query . " and ct.date < '".$final."' ";
			}
				
			echo $query;

			$select = $db->fetchAll($query);

			return $select;
		} catch (Exception $e) {
			echo $e;
			die;
		}

	}

	public function getStatus()
	{
		try {
			$db = Zend_Db_Table::getDefaultAdapter();

			$query = $db->select()
			->from(array('s' => 'tr_status'));
			$select = $db->fetchAll($query);
			return $select;
		} catch (Exception $e) {
		}
	}

	public function getAllClients()
	{
		try {
			$db = Zend_Db_Table::getDefaultAdapter();

			$query = $db->select()
			->from(array('c' => 'cl_client'));
			$select = $db->fetchAll($query);
			return $select;
		} catch (Exception $e) {
		}
	}

	public function getClients($search)
	{
		//Adaptador para o banco (Tabela não existe na nossa Base)
		try {

			$db = Zend_Db_Table::getDefaultAdapter();
			//Query de consulta
			$query = " SELECT * FROM cl_client where name like '%".$search."%'";

			$select = $db->fetchAll($query);
			return $select;
		} catch (Exception $e) {
			echo $e;
		}
	}

	public function getUserTypes()
	{
		try {
			$db = Zend_Db_Table::getDefaultAdapter();

			$query = $db->select()
			->from(array('t' => 'us_type'))
			->order('t.type ASC');
			$select = $db->fetchAll($query);
			return $select;
		} catch (Exception $e) {
		}
	}

	public function findProductById($id) {
		try {
			$category = new Category();
			$db = Zend_Db_Table::getDefaultAdapter();
			$query = $db->select()
			->from('pr_product')
			->where('id_product = ?', $id);
			$result = $db->fetchAll($query);
			return $result;
		} catch (Exception $e) {
		}
	}

	public function findClientById($id) {
		try {
			$category = new Category();
			$db = Zend_Db_Table::getDefaultAdapter();
			$query = $db->select()
			->from(array('c' => 'cl_client'))
			->join(array('a' => 'cl_address'),
                    'c.id_client = a.id_client')
			->where('c.id_client = ?', $id);
			$result = $db->fetchAll($query);
			return $result;
		} catch (Exception $e) {
		}
	}

	public function getEnterpriseById($id) {
		try {
			$category = new Category();
			$db = Zend_Db_Table::getDefaultAdapter();
			$query = $db->select()
			->from('pr_enterprise')
			->where('enterprise_id = ?', $id);
			$result = $db->fetchAll($query);
			return $result;
		} catch (Exception $e) {
		}
	}

	public function getProductById($id){
		try {
			$category = new Category();
			$db = Zend_Db_Table::getDefaultAdapter();
			$query = $db->select()
			->from('pr_product')
			->where('id_product = ?', $id);
			$result = $db->fetchAll($query);
			return $result;
		} catch (Exception $e) {
		}
	}

	public function changeUserType($userId, $userType){
		try {

			$db = Zend_Db_Table::getDefaultAdapter();
			$query = "UPDATE cl_client SET user_type = ".$userType." WHERE id_client=".$userId."";
			$db->query($query);
		} catch (Exception $e) {
			echo $e;
		}
	}

	public function changeAttempt($data){
		
		try {
			$db = Zend_Db_Table::getDefaultAdapter();

			$where = $db->quoteInto('id_transaction = ?', $data['id_transaction']);

			$db->update('cl_transaction', $data, $where);
			
		} catch(Exception $e) {
			Zend_Registry::get('log')->info('updateTransaction() Erro ao atualizar transação'.$e);
		}

	}

	public function getProductsByTransaction($idTransaction){
		try{

			$db = Zend_Db_Table::getDefaultAdapter();

			$select = $db->fetchAll("SELECT DISTINCT *, pp.name as pr_name FROM carmezin_project.cl_cart_mirror cm
			inner join cl_transaction ct
			on ct.id_transaction = cm.id_transaction
			inner join cl_transaction_address ta
			on ta.id_transaction = ct.id_transaction
			inner join pr_product pp
			on pp.id_product = cm.id_product
			inner join cl_client cc
			on cc.id_client = cm.id_client
			where ct.id_transaction = '".$idTransaction."'");

			return $select;


		}catch (Exception $e){
			echo $e;
		}
	}
}